package cn.dy.workatten.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import cn.dy.base.framework.db.DBConnectPool;
import cn.dy.base.framework.db.DBUtil;
import cn.dy.base.framework.db.util.DBExecuteUtil;
import cn.dy.workatten.pojo.ScheduleCfg;

public class ScheduleCfgDao {

	protected static Logger logger = LoggerFactory.getLogger(ScheduleCfgDao.class);

	private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedJdbcTemplate;

	public ScheduleCfgDao() {
		this.dataSource = DBConnectPool.create().getDataSource();
		if (this.jdbcTemplate == null)
			this.jdbcTemplate = new JdbcTemplate(this.dataSource);
		if (this.namedJdbcTemplate == null) {
			this.namedJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
		}
	}

	/**
	 * 获得自增系列
	 * 
	 * @return
	 */
	public long getUniqueId() {
		long result = 0L;
		try {
			result = DBUtil.getSequence(new JdbcTemplate(this.dataSource), "seq_bz_schedule_cfg");
		} catch (DataAccessException e) {
			logger.error("获得班次配置自动生成的ID值失败", e);
		}
		return result;
	}

	/**
	 * 新增
	 * 
	 * @param scheduleCfg
	 * @return
	 */
	public int addScheduleCfg(ScheduleCfg scheduleCfg) {
		DBExecuteUtil db = new DBExecuteUtil();
		int result = 0;

		try {
			String sql = "insert into bz_schedule_cfg(id,schedule_name,schedule_type,symbol,man_hour,ratio,remark) values(:id,:schedule_name,:schedule_type,:symbol,:man_hour,:ratio,remark)";

			Map resultMap = new HashMap();
			resultMap.put("id", getUniqueId());
			resultMap.put("schedule_name", scheduleCfg.getSchedule_name());
			resultMap.put("schedule_type", scheduleCfg.getSchedule_type());
			resultMap.put("symbol", scheduleCfg.getSymbol());
			resultMap.put("man_hour", scheduleCfg.getMan_hour());
			resultMap.put("ratio", scheduleCfg.getRatio());
			resultMap.put("remark", scheduleCfg.getRemark());
			result = db.executeSql(sql, resultMap);
		} catch (DataAccessException e) {
			logger.error("添加班次配置失败", e);
		}
		return result;
	}

	/**
	 * 修改
	 * 
	 * @param scheduleCfg
	 * @return
	 */
	public int updateScheduleCfg(ScheduleCfg scheduleCfg) {
		DBExecuteUtil db = new DBExecuteUtil();
		int result = 0;
		try {
			String sql = "update bz_schedule_cfg set schedule_name=:schedule_name,schedule_type=:schedule_type,symbol=:symbol,man_hour=:man_hour,ratio=:ratio,remark=:remark where id=:id";

			Map resultMap = new HashMap();
			resultMap.put("id", Long.valueOf(scheduleCfg.getId()));
			resultMap.put("schedule_name", scheduleCfg.getSchedule_name());
			resultMap.put("schedule_type", scheduleCfg.getSchedule_type());
			resultMap.put("symbol", scheduleCfg.getSymbol());
			resultMap.put("man_hour", scheduleCfg.getMan_hour());
			resultMap.put("ratio", scheduleCfg.getRatio());
			resultMap.put("remark", scheduleCfg.getRemark());
			result = db.executeSql(sql, resultMap);
		} catch (DataAccessException e) {
			logger.error("修改班次配置失败", e);
		}
		return result;
	}

	/**
	 * 删除
	 * 
	 * @param id
	 * @return
	 */
	public int deleteScheduleCfgById(long id) {
		DBExecuteUtil db = new DBExecuteUtil();
		int result = 0;
		try {
			String sql = " delete from bz_schedule_cfg where id=:id";
			Map resultMap = new HashMap();
			resultMap.put("id", Long.valueOf(id));
			result = db.executeSql(sql, resultMap);
		} catch (DataAccessException e) {
			logger.error("删除班次配置失败", e);
		}
		return result;
	}

	/**
	 * 根据id查询班次配置信息
	 * 
	 * @param id
	 * @return
	 */
	public ScheduleCfg getScheduleCfgById(long id) {
		DBExecuteUtil db = new DBExecuteUtil();
		ScheduleCfg scheduleCfg = new ScheduleCfg();
		try {
			String sql = "select * from bz_schedule_cfg where id=:id ";

			Map parameters = new HashMap();
			parameters.put("id", Long.valueOf(id));
			scheduleCfg = (ScheduleCfg) db.queryObject(sql, parameters, ScheduleCfg.class);
		} catch (DataAccessException e) {
			logger.error("根据id获得班次配置失败", e);
		}
		return scheduleCfg;
	}

	/**
	 * 分页查询
	 * 
	 * @param pageNum  第几页
	 * @param pageSize 一页记录数
	 * @return
	 */
	public Object queryScheduleCfgByPage(String schedule_type, String schedule_name, int pageNum, int pageSize) {
		Map resultMap = new HashMap();
		int total = 0;
		List<ScheduleCfg> list = new ArrayList();
		try {

			String sql = "select * from bz_schedule_cfg where 1 = 1 ";
			if (StringUtils.isNotEmpty(schedule_type)) {
				sql = sql + " and schedule_type=:scheduletype";
			}
			if (StringUtils.isNotEmpty(schedule_name)) {
				sql = sql + " and schedule_name like :schedulename";
			}
			
			Map<String, Object> params = new HashMap();
			params.put("scheduletype", schedule_type);
			params.put("schedulename", "%" + schedule_name + "%" );
			
			total = this.namedJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper(ScheduleCfg.class)).size();
			
			if (pageSize == 0) {
				list = this.namedJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper(ScheduleCfg.class));
			} else {
				list = this.namedJdbcTemplate.query(DBUtil.getPagingSql(sql.toString()),
						DBUtil.parsePagingParam(params, pageNum, pageSize), new BeanPropertyRowMapper(ScheduleCfg.class));
			}
		} catch (DataAccessException e) {
			logger.error("查询班次配置清单", e);
		}
		resultMap.put("total", Integer.valueOf(total));
		resultMap.put("list", list);

		return resultMap;
	}

	public List<ScheduleCfg> getAllScheduleCfg() {
		String sql = "select * from bz_schedule_cfg";
		BeanPropertyRowMapper<ScheduleCfg> rowMapper = new BeanPropertyRowMapper(ScheduleCfg.class);
		List<ScheduleCfg> list = this.jdbcTemplate.query(sql, rowMapper);
		return list;
	}
	
	
	public List<ScheduleCfg> getScheduleCfgByType(int schedule_type) {
		StringBuffer sb = new StringBuffer();
		sb.append("select * from bz_schedule_cfg where schedule_type=:schedule_type");
		Map<String, Object> paramters20 = new HashMap();
		paramters20.put("schedule_type", schedule_type);
		List<ScheduleCfg> list = this.namedJdbcTemplate.query(sb.toString(), paramters20,
				new BeanPropertyRowMapper(ScheduleCfg.class));
		return list;
	}

}
